#!/usr/bin/perl

use DBI;
use strict;
#use PGPLOT;

print "[main] database_access.pl\n\n";

my $dsn="dbi:Pg:dbname=flux_monitoring;host=localhost;port=5432";
my $user="postgres";
my $password="";

# Set up a connection to the database listing the RPFITS files that need to be
# processed.
my $dbh=DBI->connect($dsn,$user,$password,{ RaiseError => 0, AutoCommit => 1});

# search for what action to take
my $action=0;
my $argument;
my %restrictions;
for (my $i=0;$i<=$#ARGV;$i++){
    if ($ARGV[$i] eq "--source-list"){
	$action=1;
    } elsif ($ARGV[$i] eq "--source-flux"){
	$action=2;
	$i++;
	$argument=$ARGV[$i];
    } elsif ($ARGV[$i] eq "--project-list"){
	$action=3;
    } elsif ($ARGV[$i] eq "--restrict-band"){
	$i++;
	$restrictions{'band'}=$ARGV[$i];
    } elsif ($ARGV[$i] eq "--restrict-project"){
	$i++;
	$restrictions{'project'}=$ARGV[$i];
    } elsif ($ARGV[$i] eq "--restrict-primary"){
	$i++;
	$restrictions{'primary'}=$ARGV[$i];
    }
}

if ($action==1){
    # generate a list of sources in the fluxes table
    my @source_list=&fluxes_get_source_list($dbh,%restrictions);
    print "[main] Sources found in fluxes table:\n";
    for (my $i=0;$i<=$#source_list;$i++){
	print "[main] source found: ".$source_list[$i]."\n";
    }
} elsif ($action==2){
    # generate a list of fluxes for the specified source
    print "[main] Searching for fluxes for source ".$argument."\n";
    if ($argument eq ""){
	die ("[main-error] source not specified\n");
    }
    my @flux_data=&fluxes_get_source_fluxes($argument,$dbh,%restrictions);
    print "[main] Fluxes for source ".$argument.":\n";
    for (my $i=0;$i<=$#flux_data;$i++){
	print " ".$flux_data[$i]{'obstime'}."  ".$flux_data[$i]{'project'}.
	    "  ".$flux_data[$i]{'frequency'}."  ".$flux_data[$i]{'flux_scalar'}.
	    "  ".$flux_data[$i]{'flux_vector'}."  ".$flux_data[$i]{'primary_name'}."\n";
    }
} elsif ($action==3){
    # generate a list of project codes in the fluxes table
    my @project_list=&fluxes_get_project_list($dbh,%restrictions);
    print "[main] Projects found in fluxes table:\n";
    for (my $i=0;$i<=$#project_list;$i++){
	print "[main] project found: ".$project_list[$i]."\n";
    }
}

$dbh->disconnect;

exit;

sub restrictions_to_sql_string {
    # turn the restrictions hash into an SQL query string
    my ($add_where,%restrictions)=@_;
    my $sql_query_string="";
    if ($add_where==1){
	if (($restrictions{'band'} ne "")||
	    ($restrictions{'project'} ne "")||
	    ($restrictions{'primary'} ne "")){
	    $sql_query_string.="WHERE ";
	}
    }
    if ($restrictions{'band'} ne ""){
	# frequency based restrictions
	if ($add_where==0){
	    $sql_query_string.="AND ";
	} else {
	    $add_where=0;
	}
	my ($minimum_frequency,$maximum_frequency);
	if ($restrictions{'band'} eq "cm"){
	    $minimum_frequency=0;
	    $maximum_frequency=10000;
	} elsif ($restrictions{'band'} eq "mm"){
	    $minimum_frequency=14000;
	    $maximum_frequency=110000;
	} elsif ($restrictions{'band'} eq "20cm"){
	    $minimum_frequency=1000;
	    $maximum_frequency=2000;
	} elsif ($restrictions{'band'} eq "13cm"){
	    $minimum_frequency=2000;
	    $maximum_frequency=4000;
	} elsif ($restrictions{'band'} eq "6cm"){
	    $minimum_frequency=4500;
	    $maximum_frequency=7000;
	} elsif ($restrictions{'band'} eq "3cm"){
	    $minimum_frequency=8000;
	    $maximum_frequency=10000;
	} elsif ($restrictions{'band'} eq "12mm"){
	    $minimum_frequency=14000;
	    $maximum_frequency=27000;
	} elsif ($restrictions{'band'} eq "7mm"){
	    $minimum_frequency=27000;
	    $maximum_frequency=50000;
	} elsif ($restrictions{'band'} eq "3mm"){
	    $minimum_frequency=80000;
	    $maximum_frequency=110000;
	}
	$sql_query_string.="((frequency > ".$minimum_frequency.") AND ".
	    "(frequency < ".$maximum_frequency.")) ";
    }
    if ($restrictions{'project'} ne ""){
	# project code based restrictions
	if ($add_where==0){
	    $sql_query_string.="AND ";
	} else {
	    $add_where=0;
	}
	$sql_query_string.="project = '".$restrictions{'project'}."' ";
    }
    if ($restrictions{'primary'} ne ""){
	# primary calibrator based restrictions
	if ($add_where==0){
	    $sql_query_string.="AND ";
	} else {
	    $add_where=0;
	}
	$sql_query_string.="primary_name = '".$restrictions{'primary'}."' ";
    }

    return $sql_query_string;
}

sub fluxes_get_source_fluxes {
    # this routine gets all fluxes for the given source
    my ($sourcename,$dbh,%restrictions)=@_;

    my $gsf_query="SELECT * FROM fluxes WHERE calibrator='".$sourcename."' ";
    $gsf_query.=&restrictions_to_sql_string(0,%restrictions);
    # print "[fluxes_get_source_fluxes] SQL query is <".$gsf_query.">\n";
    my $gsf=$dbh->prepare($gsf_query);
    $gsf->execute;

    my @flux_data;
    while (my @source_info=$gsf->fetchrow()){
	push @flux_data,{
	    obstime => $source_info[2],
	    project => $source_info[3],
	    frequency => $source_info[4],
	    primary_name => $source_info[5],
	    flux_scalar => $source_info[15],
	    flux_vector => $source_info[16]
	    };
    }
    $gsf->finish;

    return @flux_data;
}

sub fluxes_get_source_list {
    # this routine goes through the fluxes table and gets a list of all
    # sources that have data in it
    my ($dbh,%restrictions)=@_;

    my $gfn_query="SELECT calibrator FROM fluxes ";
    $gfn_query.=&restrictions_to_sql_string(1,%restrictions);
    # print "[fluxes_get_source_fluxes] SQL query is <".$gfn_query.">\n";
    my $gfn=$dbh->prepare($gfn_query);
    $gfn->execute;

    my @source_list;
    while (my @flux_elements=$gfn->fetchrow()){
	my $sourcename=$flux_elements[0];
	my $sourcepresent=0;
	for (my $i=0;$i<=$#source_list;$i++){
	    if ($source_list[$i] eq $sourcename){
		$sourcepresent=1;
		last;
	    }
	}
	if ($sourcepresent==0){
	    push @source_list,$sourcename;
	}
    }
    $gfn->finish;

    return @source_list;
}

sub fluxes_get_project_list {
    # this routine goes through the fluxes table and gets a list of all
    # sources that have data in it
    my ($dbh,%restrictions)=@_;

    my $gfn_query="SELECT project FROM fluxes ";
    $gfn_query.=&restrictions_to_sql_string(1,%restrictions);
    print "[fluxes_get_source_fluxes] SQL query is <".$gfn_query.">\n";
    my $gfn=$dbh->prepare($gfn_query);
    $gfn->execute;

    my @project_list;
    while (my @flux_elements=$gfn->fetchrow()){
	my $projectname=$flux_elements[0];
	my $projectpresent=0;
	for (my $i=0;$i<=$#project_list;$i++){
	    if ($project_list[$i] eq $projectname){
		$projectpresent=1;
		last;
	    }
	}
	if ($projectpresent==0){
	    push @project_list,$projectname;
	}
    }
    $gfn->finish;

    return @project_list;
}
